package com.portal.dao;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleTypes;
import org.apache.log4j.Logger;

import com.portal.dto.ComboDTO;
import com.portal.dto.ComunaDTO;
import com.portal.dto.RegionDTO;
import com.portal.dto.UsuarioDTO;

import utiles.AppProperties;
import utiles.ConexionPool;

public class CombosPortalDAO {

	private static Logger log = Logger.getLogger(CombosPortalDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	//String servlTargetUPExa = (AppProperties.getProperty("aplicacion", "servlTargetUP"));
	//String fileUrlExa       = (AppProperties.getProperty("aplicacion", "fileUrlExa"));
	
	public List<RegionDTO> getAllRegion() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
		
		List<RegionDTO> listRegion = new ArrayList<RegionDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_por_combos.getAllRegion(?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.registerOutParameter(1, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(1);

			while (rs.next()) {
				RegionDTO regionDTO = new RegionDTO();
				regionDTO.setIdRegion(rs.getString(1));
				regionDTO.setDescripcion(rs.getString(2));				
				listRegion.add(regionDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CombosPortalDAO.getAllRegion SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listRegion;

	}
	
	public List<ComunaDTO> getAllComunas() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<ComunaDTO> listComuna = new ArrayList<ComunaDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_por_combos.getAllComunas(?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.registerOutParameter(1, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(1);

			while (rs.next()) {
				ComunaDTO comunaDTO = new ComunaDTO();
				comunaDTO.setIdComuna(rs.getString(1));
				comunaDTO.setDescripcion(rs.getString(2));			
				listComuna.add(comunaDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CombosPortalDAO.getAllComunas SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listComuna;

	}
	
	public List<ComunaDTO> getComunasByRegion(String region) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<ComunaDTO> listComuna = new ArrayList<ComunaDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_por_combos.getComunasByRegion(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, region);				
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);

			while (rs.next()) {
				ComunaDTO comunaDTO = new ComunaDTO();
				comunaDTO.setIdComuna(rs.getString(1));
				comunaDTO.setDescripcion(rs.getString(2));			
				listComuna.add(comunaDTO);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CombosPortalDAO.getComunasByRegion SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listComuna;
	}

	public List<ComboDTO> getAllCombos() throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".Pkg_por_combos.getAllCombos(?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.registerOutParameter(1, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(1);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setId(rs.getString(1));
					perfUSR.setId2(rs.getString(2));
					perfUSR.setDesc(rs.getString(3));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error CombosPortalDAO.getAllCombos SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}
	
	public List<UsuarioDTO> getSedeAreaByServ(String idServ) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<UsuarioDTO> listRet = new ArrayList<UsuarioDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_por_combos.getSedeAreaByIdServicio(?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, idServ);				
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(2);

			while (rs.next()) {
				UsuarioDTO usu = new UsuarioDTO();
				usu.setIdSede(rs.getString(1));
				usu.setIdArea(rs.getString(2));
				
				/*ComunaDTO comunaDTO = new ComunaDTO();
				comunaDTO.setIdComuna(rs.getString(1));
				comunaDTO.setDescripcion(rs.getString(2));			
				listComuna.add(comunaDTO);*/
				listRet.add(usu);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error CombosPortalDAO.getSedeAreaByIdServicio SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listRet;
	}	
	
	public List<ComboDTO> getParametro(String idPar) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		List<ComboDTO> listUserPerf = new ArrayList<ComboDTO>();
			try {
				String query = "{ call "+pkgVar+".Pkg_por_combos.getParametro(?, ?) }"; 	       
				CallableStatement  stmt = conn.prepareCall(query);
				stmt.setString(1, idPar);				
				stmt.registerOutParameter(2, OracleTypes.CURSOR);
				stmt.execute();
				ResultSet rs = (ResultSet)stmt.getObject(2);
				while (rs.next()) {
					ComboDTO perfUSR = new ComboDTO();
					perfUSR.setDesc(rs.getString(1));
					listUserPerf.add(perfUSR);
	            }
	            rs.close();
	            stmt.close();
	        } catch (SQLException ex) {  
	           log.error("Error CombosPortalDAO.getParametro SQLException: " +ex.getMessage());
	        	System.out.println(ex.getMessage());
	        } finally {
	        	//ConexionPool.desconectaPool(conn);
				conn.close();
				conn = null;
	        }
	        return listUserPerf;		
		}
	
}

